from selenium.webdriver.common.by import By
from selenium import webdriver
import pymysql
import time

conn = pymysql.connect(host='localhost',password='123456',user='root',database='spider')
cursor = conn.cursor()
cursor.execute("create table 沪深A股(序号 varchar(20),股票代码 varchar(20),股票名称 varchar(20),最新报价 varchar(20),涨跌幅 varchar(20),涨跌额 varchar(20),成交量 varchar(20),成交额  varchar(20),振幅  varchar(20),最高 varchar(20),最低 varchar(20),今开 varchar(20),昨收 varchar(20))")
cursor.execute("create table 上证A股(序号 varchar(20),股票代码 varchar(20),股票名称 varchar(20),最新报价 varchar(20),涨跌幅 varchar(20),涨跌额 varchar(20),成交量 varchar(20),成交额  varchar(20),振幅  varchar(20),最高 varchar(20),最低 varchar(20),今开 varchar(20),昨收 varchar(20))")
cursor.execute("create table 深证A股(序号 varchar(20),股票代码 varchar(20),股票名称 varchar(20),最新报价 varchar(20),涨跌幅 varchar(20),涨跌额 varchar(20),成交量 varchar(20),成交额  varchar(20),振幅  varchar(20),最高 varchar(20),最低 varchar(20),今开 varchar(20),昨收 varchar(20))")
conn.commit()
sql1 = "INSERT INTO 沪深A股 (序号,股票代码, 股票名称,最新报价,涨跌幅,涨跌额,成交量,成交额,振幅,最高,最低,今开,昨收) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
sql2 = "INSERT INTO 上证A股 (序号,股票代码, 股票名称,最新报价,涨跌幅,涨跌额,成交量,成交额,振幅,最高,最低,今开,昨收) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
sql3 = "INSERT INTO 深证A股 (序号,股票代码, 股票名称,最新报价,涨跌幅,涨跌额,成交量,成交额,振幅,最高,最低,今开,昨收) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
url = "http://quote.eastmoney.com/center/gridlist.html#hs_a_board"
page = input("请分别输入“沪深 A 股”、“上证 A 股”、“深证 A 股”3 个板块要爬取的页数:")
pagelist = list(map(int, page.split()))
pageA = pagelist[0]
pageB = pagelist[1]
pageC = pagelist[2]
driver = webdriver.Chrome()
driver.get(url)
print("沪深 A 股")
for i in range(pageA):
    content = driver.find_elements(By.XPATH, '//*[@id="table_wrapper-table"]/tbody/tr')
    for j in content:
        list = j.text.split(" ")
        values = (
        list[0], list[1], list[2], list[6], list[7], list[8], list[9], list[10], list[11], list[12], list[13], list[14],
        list[15])
        cursor.execute(sql1, values)
        conn.commit()
    next_button = driver.find_element(By.XPATH, '//a[@class="next paginate_button"]')
    next_button.click()
    time.sleep(2)


driver.get("http://quote.eastmoney.com/center/gridlist.html#sh_a_board")
print("上证 A 股")
time.sleep(2)
for i in range(pageB):
    content = driver.find_elements(By.XPATH, '//*[@id="table_wrapper-table"]/tbody/tr')
    for j in content:
        list = j.text.split(" ")
        values = (
            list[0], list[1], list[2], list[6], list[7], list[8], list[9], list[10], list[11], list[12], list[13],
            list[14],
            list[15])
        cursor.execute(sql2, values)
        conn.commit()
    next_button = driver.find_element(By.XPATH, '//a[@class="next paginate_button"]')
    next_button.click()
    time.sleep(2)

driver.get("http://quote.eastmoney.com/center/gridlist.html#sz_a_board")
print("深证 A 股")
time.sleep(2)
for i in range(pageC):
    content = driver.find_elements(By.XPATH, '//*[@id="table_wrapper-table"]/tbody/tr')
    for j in content:
        list = j.text.split(" ")
        values = (
            list[0], list[1], list[2], list[6], list[7], list[8], list[9], list[10], list[11], list[12], list[13],
            list[14],
            list[15])
        cursor.execute(sql3, values)
        conn.commit()
    next_button = driver.find_element(By.XPATH, '//a[@class="next paginate_button"]')
    next_button.click()
    time.sleep(2)



cursor.close()
conn.close()